package py.excelHandle;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import py.ColumnStrategy;
import py.SwitchConfig;
import py.pathMaker.PathMaker;
import py.util.ExcelConfig;
import py.util.ToolUtil;

public class ExcelCallable03 implements I_ExcelCallable {
	private File sorceExc = null;
	private String parentPath="";
	private String resultPath = "";
	private String noDealPath = "";
	private ArrayList sheetList = new ArrayList();
	private PathMaker pathMaker=null;

	public ExcelCallable03(File sorceFile,PathMaker pathMaker) {
		this.sorceExc = sorceFile;
		this.pathMaker=pathMaker;
	}
	


	/**
	 * 异步接口
	 */
	public String call() throws Exception {
		StringBuffer sb = new StringBuffer();
		if (this.sorceExc.isFile()) {
			this.parentPath = pathMaker.getResultPath(sorceExc.getAbsolutePath());
			this.resultPath = (parentPath + File.separator + "result");
			this.noDealPath = (parentPath + File.separator + "noDeal");
		}
		if (!pathMaker.createDir(this.resultPath)) {
			sb.append("resultPath is Error");
		}
		if (!pathMaker.createDir(this.noDealPath)) {
			sb.append("noDealPath is Error");
		}
		boolean isSuccess = doExcel();
		if (isSuccess) {
			sb.append("Switch Success the File:" + this.sorceExc.getName());
		} else {
			copyFile2UnDone();
			sb.append("Done Error:File Copy to:" + this.noDealPath);
		}
		return sb.toString();
	}
	
	/**
	 * 同步接口
	 * @return
	 * @throws IOException 
	 * @throws BiffException 
	 */
	public String callsyn() throws IOException
	{
		StringBuffer sb = new StringBuffer();
		if (this.sorceExc.isFile()) {
			this.parentPath = pathMaker.getResultPath(sorceExc.getParentFile().getAbsolutePath());
			this.resultPath = (parentPath + File.separator + "result");
			this.noDealPath = (parentPath + File.separator + "noDeal");
		}
		if (!pathMaker.createDir(this.resultPath)) {
			sb.append("resultPath is Error");
		}
		if (!pathMaker.createDir(this.noDealPath)) {
			sb.append("noDealPath is Error");
		}
		boolean isSuccess = doExcel();
		if (isSuccess) {
			sb.append("Switch Success the File:" + this.sorceExc.getName());
		} else {
			copyFile2UnDone();
			sb.append("Done Error:File Copy to:" + this.noDealPath);
		}
		return sb.toString();
	}

	/**
	 * 如果转换失败则将文件复制到noDeal文件夹
	 * 
	 */
	private void copyFile2UnDone() {
		File noDealFile = createFile(false);
		byte[] buffer = new byte[1024];
		int byteread = 0; // 读取的字节数
		InputStream in =null;
		OutputStream out =null;
		try {
			in = new FileInputStream(this.sorceExc);
			out = new FileOutputStream(noDealFile);
			while ((byteread = in.read(buffer)) != -1) {
				out.write(buffer, 0, byteread);
			}

		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try{
				if(in!=null)
				{
					in.close();
				}
				if(out!=null)
				{
					out.close();
				}
			} catch (IOException e) {  
				e.printStackTrace();  
			}
					
		}

	}

	private boolean doExcel() throws IOException {
		boolean isSuccess = false;
		//获取原始Excel数据
		POIFSFileSystem fs=new POIFSFileSystem(this.sorceExc);
		HSSFWorkbook readBook = new HSSFWorkbook(fs);
		
		HSSFSheet[] sheets =new HSSFSheet[readBook.getNumberOfSheets()] ; //被分析文件的sheet总数
		Iterator<org.apache.poi.ss.usermodel.Sheet> sheetIt=readBook.sheetIterator();
		int sheetNum=0;
		while(sheetIt.hasNext())
		{
			sheets[sheetNum]=(HSSFSheet)sheetIt.next();
			sheetNum++;
		}
		
		
//		HSSFSheet[] arrayOfSheet1;
//		int j = (arrayOfSheet1 = sheets).length;
		//迭代每个sheet页
		for (int i = 0; i < sheets.length; i++) {  
			HSSFSheet sheet = sheets[i];
			int line = 0;
			HSSFRow titleRow=sheet.getRow((line = SwitchConfig.getTitleLine() - 1) >= 0 ? line : 0);//获取标题列
			int columNum=0;
			//空sheet会抛异常
			try{
				columNum = titleRow.getLastCellNum(); //获取多少列
			}
			catch(Exception ex)
			{
				System.out.println("***********"+ex.getMessage()+"***********");
//				System.out.println("File:"+this.sorceExc+";SheetNo:"+i+"; rowNum:"+titleRow.getRowNum());
//				continue;
			}
//			int columns = titleRow.getLastCellNum(); //获取多少列
			int rows = sheet.getLastRowNum();         //获取多少行
			if ((columNum == 0) || (rows <1)) {       //没有数据的直接忽略
				ArrayList noDoneList = new ArrayList();
				this.sheetList.add(noDoneList);
			} else {
				HSSFCell[] titleCells = ToolUtil.getCellsOfRow(titleRow);
				ArrayList searchColunm = checkTitle(titleCells); // searchColunm是该Sheet中与目标列对应的源列的顺序号
				if (searchColunm != null) {
					this.sheetList.add(searchColunm);
					isSuccess = true;
				} else {
					ArrayList noDoneList = new ArrayList();
					this.sheetList.add(noDoneList);
				}
			}
		}
		if (isSuccess) {
			File aimFile = createFile(true);
			isSuccess = beginSwitch(aimFile, sheets);
		}
		readBook.close();
		return isSuccess;
	}


	/**
	 * 完成目标文件的生成
	 * 
	 * @param aimFile
	 * @param sheets
	 * @return
	 */
	private boolean beginSwitch(File aimFile, HSSFSheet[] sheets) {
		SXSSFWorkbook wwb = new SXSSFWorkbook(1000);   //结果Excel文件
		SXSSFSheet writeableSheet = null;
		boolean isOK = true;
		List<ColumnStrategy> desColList = SwitchConfig.getDesCols();
		try {
//			wwb = Workbook.createWorkbook(aimFile);
			for (int i = 0; i < sheets.length; i++) {
				List searchColunm = (ArrayList) this.sheetList.get(i);
				if (searchColunm.size() > 0) // 过滤掉没有内容的sheet
				{
					HSSFSheet st = sheets[i];
					writeableSheet = wwb.createSheet(st.getSheetName());
					int rowNum = st.getLastRowNum();      //共有多少行
					//删除尾部空行
//					for(int removeid=rowNum;removeid<1;removeid--)
//					{
//						HSSFRow readRow=st.getRow(removeid);
//						boolean isBlank=true;
//						for (int t=0;t<readRow.getLastCellNum();t++)
//						{
//							String value=ToolUtil.getCellValue(readRow.getCell(t)).trim();
//							if(value.length()>0)
//							{
//								isBlank=false;
//								break;
//							}
//						}
//						if(isBlank)
//							rowNum--;
//					}
					//end 删除尾部空行
					
					
//					int colNum =st.getColumns();    //共有多少列
					for (int r = SwitchConfig.getTitleLine() - 1; r < rowNum+1; r++) // 按照每一行迭代
					{
//						HSSFCell[] cs = ToolUtil.getCellsOfRow(st.getRow(r)); //获取一行的所有单元格
						HSSFRow readRow=st.getRow(r);
						//判断是否有空行
						boolean isBlank=true;
						for (int t=0;t<readRow.getLastCellNum();t++)
						{
							String value=ToolUtil.getCellValue(readRow.getCell(t)).trim();
							if(value.length()>0)
							{
								isBlank=false;
								break;
							}
						}
						if(isBlank)
							continue;
						//判断结束
						SXSSFRow writeRow=writeableSheet.createRow(r);
						
						for(int sc=0;sc<searchColunm.size();sc++)  //从一行中获取所需的单元格
						{				
							
							int sourceCoNum=(int) searchColunm.get(sc);

							HSSFCell cell = readRow.getCell(sourceCoNum);
							if(r==SwitchConfig.getTitleLine()-1)
							{
								ColumnStrategy cols=desColList.get(sc);
								writeRow.createCell(sc).setCellValue(cols.getDesColName().trim());
							}
							else
							{
								SXSSFCell newCell=writeRow.createCell(sc);
//								if((cell.getCellType()==HSSFCell.CELL_TYPE_FORMULA)
//										&&!HSSFDateUtil.isCellDateFormatted(cell)
//										&&(cell.getCellStyle().getDataFormat()!= 58))
//								{
//									CellStyle cellStyle=wwb.createCellStyle();
//									short text = 0x31; 
//									cellStyle.setDataFormat(text);
//									newCell.setCellStyle(cellStyle);
//								}
								newCell.setCellValue(ToolUtil.getCellValue(cell));//modify
							
							}	
						}// 结束一行中每个单元格的添加
						
					} // 完成所有行的迭代
				}
			}// 完成所有sheet的迭代

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			isOK = false;
		} finally {

			try {
				wwb.write(new FileOutputStream(aimFile));
				wwb.close();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} 
		}
		return isOK;
	}

	/**
	 * 查找与目标列对应的源文件列
	 * 
	 * @param titleCells
	 * @return
	 */
	private ArrayList checkTitle(HSSFCell[] titleCells) {
		boolean isOK = true;
		ArrayList titeList = new ArrayList();
		ArrayList searchColunm = new ArrayList();
		// 获取标头内容
		for (int i = 0; i < titleCells.length; i++) {
			if(titleCells[i]!=null)
			   titeList.add(titleCells[i].getStringCellValue().trim());
			else
				titeList.add(i);
		}
		// 获取目标列数组
		List<ColumnStrategy> desColList = SwitchConfig.getDesCols();
		for (ColumnStrategy cs : desColList) {
			List sorColNames = cs.getSorColName(); // 每个列的对应的源列名
			boolean isFind = false;
			for (Object o : sorColNames) {
				if (titeList.contains(o)) {
					searchColunm.add(Integer.valueOf(titeList.indexOf(o))); // 如果找到合适的源列名则将源列名的顺序号存入searchColunm
					isFind = true;
					break;
				}
			}
			if (!isFind) {
				isOK = false;
			}
		}
		if (!isOK) {
			return null;
		}
		return searchColunm;
	}



	private synchronized File createFile(boolean isOK) {
		String fileName = "";
		if (isOK) {
			fileName = this.resultPath + File.separator
					+ this.sorceExc.getName().substring(0, this.sorceExc.getName().lastIndexOf("."));
			fileName=fileName+".xlsx";
		} else {
			fileName = this.noDealPath + File.separator
					+ this.sorceExc.getName();
		}
		
		File aimFile = new File(fileName);
		return aimFile;
	}
}
